import requests
from bs4 import BeautifulSoup
import pymysql
import time
import datetime
import json
import re
import pandas as pd  
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats
from collections import Counter





#数据库配置
host = '127.0.0.1'
user = 'root'
password = 'root'
db = 'jmx2023'
charset = 'utf8'
port = 3306

# 连接数据库,返回连接
def get_conn():
    conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db)
    return conn

#根据连接创建游标对象，返回游标对象
def get_cur(conn):
    cursor = conn.cursor()
    return cursor


def getcolumn_list(column_name):
    try:
        conn=get_conn()
        cursor=get_cur(conn)
        sql="select "+column_name+" from qixing_lotto order by number desc "
        cursor.execute(sql)
        conn.commit()
        res=cursor.fetchall()
        # 将查询结果转换为DataFrame对象  
        df = pd.DataFrame(res, columns=[column[0] for column in cursor.description])  
        
        # 将每一列作为列表返回  
        column_lists = df.columns.tolist()  
        for column in column_lists:  
            column_values = df[column].tolist()
            #print(f"{column}: {column_values}")
        return column_values
    except Exception as e:
        print(str(e))
    finally:
        cursor.close()

def getdata_dict():
    try:
        conn=get_conn()
        cursor=get_cur(conn)
        sql="select red01,red02,red03,red04,red05,red06,blue from welfare_lotto"
        cursor.execute(sql)
        conn.commit()
        # 获取列名  
        column_names = [description[0] for description in cursor.description]   
        # 获取所有行  
        rows = cursor.fetchall()    
        # 将结果转换为字典列表  
        result = [dict(zip(column_names, row)) for row in rows]
        print(result)
        return result  
    except Exception as e:
        print(str(e))
    finally:
        cursor.close()

def find_mode(numbers):
    unique_nums = np.unique(numbers)
    counts = np.bincount(numbers)
    mode_num = unique_nums[np.argmax(counts)]
    return mode_num

red01_list=getcolumn_list("blue04")

# print(red01_list)
# arr = np.array(red01_list,dtype='i')
num_list=getcolumn_list("number")
# print(arr)
#正态分布（高斯分布）的直方图
#这些随机数的平均值（期望值）为5.0，标准差为1.0。
def zhengtaifenbu(x,y,z):
    x = np.random.normal(x,y,z)
    plt.hist(x, 100)
    plt.show()

#散点图
def sandian():
    x = getcolumn_list("id")
    y = getcolumn_list("blue04")

    plt.scatter(x, y)
    plt.show()
#sandian()

#随机散点图
def suijisandiantu():
    x = np.random.normal(5.0, 1.0, 1000)
    y = np.random.normal(10.0, 2.0, 1000)
    plt.scatter(x, y)
    plt.show()

#折线图
def zhexiantu(data):
    # 创建一个折线图  
    #data = [2, 4, 6, 8, 10, 12, 14, 16, 18, 20]
    plt.plot(data)  
    
    # 设置图表标题和坐标轴标签  
    # plt.title('折线图')  
    # plt.xlabel('数据值')  
    # plt.ylabel('值')  
  
    # 显示图表  
    plt.show()



def number01(red01_list):
    #平均数
    average = np.mean(red01_list)
    #中位数
    median = np.median(red01_list)
    #众数
    mode = find_mode(red01_list)
    #标准差
    standard = np.std(red01_list)
    #方差
    variance = np.var(red01_list)
    #百分位数   统计学中使用百分位数（Percentiles）为您提供一个数字，该数字描述了给定百分比值小于的值。
    percentiles = np.percentile(red01_list,90)


    print("平均数：",average,
        "\n","中位数：",median,
        "\n","众数：",mode,
        "\n","标准差：",standard,
        "\n","方差：",variance,
        "\n","百分位数：",percentiles)
    #zhengtaifenbu(average,standard,len(red01_list))
    #zhexiantu(arr)

# number01(red01_list)
    
# number01(arr)

    #随机直方图
def random_pic():
    x = np.random.uniform(0.0, 5.0, 500)
    plt.hist(x, 10)
    plt.show()


slope, intercept, r, p, std_err = stats.linregress(num_list, red01_list)

def myfunc(x):
    return slope * x + intercept

# mymodel = list(map(myfunc, num_list))
# next_number = myfunc(24008)
# print(next_number)
# plt.scatter(num_list, red01_list)
# plt.plot(num_list, mymodel)
# plt.show()

# print(r)


def huigui(y,x):
    # x = [5,7,8,7,2,17,2,9,4,11,12,9,6]
    # y = [99,86,87,88,111,86,103,87,94,78,77,85,86]
    print(x)
    print(y)
    

    mymodel = list(map(myfunc, x))

    plt.scatter(x, y)
    plt.plot(x, mymodel)
    plt.show()
    plt.scatter(x, y)
    plt.show()


counts = Counter(getcolumn_list("blue01"))
print(counts)






    
